using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

namespace labDict.Web.provider
{
    public class SQLHelper
    {
        private static string cs = null;

        public static SqlConnection GetConnection()
        {
            if (cs == null)
                cs = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;

            return new SqlConnection(cs);
        }

        public static IDbCommand CreateCommand(IDbConnection con, string procedureName)
        {

            IDbCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = procedureName;
            return cmd;
        }

        public static IDbDataParameter AddGuidParameter(IDbCommand cmd, string parameterName, Guid id)
        {
            return AddGuidParameter(cmd, parameterName, ParameterDirection.Input, id);
        }

        public static IDbDataParameter AddGuidParameter(IDbCommand cmd, string parameterName, ParameterDirection direction, Guid id)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.Direction = direction;
            p.Size = 36;
            if (id == Guid.Empty)
                p.Value = DBNull.Value;
            else
                p.Value = id;

            cmd.Parameters.Add(p);
            return p;
        }

        public static IDbDataParameter AddStringParameter(IDbCommand cmd, string parameterName, params object[] properties)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.DbType = DbType.String;
            p.Value = DBNull.Value;

            AddParameterProperties(p, properties);

            cmd.Parameters.Add(p);
            return p;
        }

        public static IDbDataParameter AddDecimalParameter(IDbCommand cmd, string parameterName, int precision,int scale, decimal value)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.DbType = DbType.Decimal;
            p.Precision = Byte.Parse(precision.ToString());
            
            p.Scale = Byte.Parse(scale.ToString());
            p.Value = value;// Decimal.Round(value, 2);

            cmd.Parameters.Add(p);
            return p;
        }


        public static IDbDataParameter AddDecimalParameter(IDbCommand cmd, string parameterName, int size, int precision, ParameterDirection direction, decimal value)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.Direction = direction;
            p.DbType = DbType.Decimal;
            p.Precision = Byte.Parse(precision.ToString());
            p.Size = size;
            p.Value = value;

            cmd.Parameters.Add(p);
            return p;
        }

        public static IDbDataParameter AddFixedStringParameter(IDbCommand cmd, string parameterName, int size, params object[] properties)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.DbType = DbType.StringFixedLength;
            p.Size = size;
            p.Value = DBNull.Value;

            AddParameterProperties(p, properties);

            cmd.Parameters.Add(p);
            return p;
        }

        public static IDbDataParameter AddBoolParameter(IDbCommand cmd, string parameterName, bool value)
        {
            return AddBoolParameter(cmd, parameterName, ParameterDirection.Input, value);
        }


        public static IDbDataParameter AddBoolParameter(IDbCommand cmd, string parameterName, ParameterDirection direction, bool value)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.Direction = direction;
            p.DbType = DbType.Boolean;
            p.Size = 1;
            p.Value = value;

            cmd.Parameters.Add(p);
            return p;

        }

        public static IDbDataParameter AddInt32Parameter(IDbCommand cmd, string parameterName, int value)
        {
            return AddInt32Parameter(cmd, parameterName, ParameterDirection.Input, value);
        }

        public static IDbDataParameter AddInt32Parameter(IDbCommand cmd, string parameterName, ParameterDirection direction, int value)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.Direction = direction;
            p.DbType = DbType.Int32;
            p.Size = 4;
            p.Value = value;

            cmd.Parameters.Add(p);
            return p;

        }

        public static IDbDataParameter AddByteParameter(IDbCommand cmd, string parameterName, byte value)
        {
            return AddByteParameter(cmd, parameterName, ParameterDirection.Input, value);
        }

        public static IDbDataParameter AddByteParameter(IDbCommand cmd, string parameterName, ParameterDirection direction, byte value)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.Direction = direction;
            p.DbType = DbType.Byte;
            p.Size = 1;
            p.Value = value;

            cmd.Parameters.Add(p);
            return p;

        }


        public static IDbDataParameter AddDateTimeParameter(IDbCommand cmd, string parameterName, params object[] properties)
        {
            IDbDataParameter p = cmd.CreateParameter();
            p.ParameterName = parameterName;
            p.DbType = DbType.DateTime;
            p.Size = 8;
            p.Value = DBNull.Value;

            AddParameterProperties(p, properties);

            cmd.Parameters.Add(p);
            return p;

        }

        public static DataTable GetResult(string select)
        {
            DataTable dt = new DataTable();
            
            SqlDataAdapter da = new SqlDataAdapter(select, GetConnection().ConnectionString);
            da.Fill(dt);
            
            return dt;

        }

        public static bool UpdateResult(string update)
        {
            bool success = true;
            
            try
            {
                SqlConnection conn = GetConnection();
                conn.Open();

            
                SqlCommand sqlCommandUpdate = new SqlCommand(update, conn);
                sqlCommandUpdate.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                success = false;
            }
            return success;
        }

        public static int GetNewIndex(string tableName, string indexName)
        {
            bool success = true;

                SqlConnection conn = GetConnection();
                conn.Open();

                if ((tableName != null && tableName != "") && (indexName != null && indexName != ""))
                {
                    // create the dataset command object
                    // and the DataSet
                    SqlDataAdapter dataAdapter = new SqlDataAdapter("Select MAX(" + indexName + ") as tblIndex from " + tableName, conn);

                    DataSet dataSet = new DataSet();

                    // fill the dataset object
                    dataAdapter.Fill(dataSet, tableName);

                    // Get the one table from the DataSet
                    DataTable dataTable = dataSet.Tables[0];

                    if (dataSet.Tables[0].Rows.Count != 0)
                    {
                        DataRow dataRow = dataSet.Tables[0].Rows[0];
                        return Int32.Parse(dataRow.ItemArray[0].ToString()) + 1;
                    }
                    else
                    {
                        throw new Exception("Invalid table or index name");
                    }


                }
                else {
                    throw new Exception("Invalid table or index name");
                }
        }

        public static bool InsertResult(string insert)
        {
            bool success = true;
            
            try
            {
                SqlConnection conn = GetConnection();
                conn.Open();
            
                SqlCommand sqlCommandInsert = new SqlCommand(insert, conn);
                sqlCommandInsert.ExecuteNonQuery();
                conn.Close();
            }
            catch (Exception ex)
            {
                success = false;
            }
            return success;
        }

        private static void AddParameterProperties(IDbDataParameter p, object[] properties)
        {

            if (properties == null)
                throw new NullReferenceException(String.Format("Parameter {0} requires properties.", p.ParameterName));

            bool sizeSet = false;
            for (int i = 0; i < properties.Length; ++i)
            {
                object o = properties[i];
                if (o == null)
                    continue;

                Type t = o.GetType();
                if (t.Equals(typeof(ParameterDirection)))
                {
                    p.Direction = (ParameterDirection)o;
                }
                else if (t.Equals(typeof(int)))
                {
                    p.Size = (int)o;
                    sizeSet = true;
                }
                else if (t.Equals(typeof(string)))
                {
                    string value = (string)o;
                    p.Value = value;
                    if (!sizeSet)
                        p.Size = value.Length;
                }
                else if (t.Equals(typeof(DateTime)))
                {
                    p.Value = (DateTime)o;
                }

            }

        }

    }
}